"""
石景山区地图数据处理脚本（综合版）
功能：读取Excel -> 拆分楼宇 -> 生成地图数据 -> 导出文件
作者：AI助手
日期：2025年11月3日
"""

import pandas as pd
import json
import sys

# 设置输出编码
sys.stdout.reconfigure(encoding='utf-8')

def main():
    print("="*80)
    print("石景山区地图数据处理")
    print("="*80)
    
    # 1. 读取Excel
    print("\n[1/4] 读取Excel数据...")
    df = pd.read_excel('楼宇工作站统计汇总表.xlsx', sheet_name='Sheet1')
    
    # 数据清洗
    df['填报单位'] = df['填报单位'].ffill().astype(str).str.replace('\n', '').str.strip()
    df['工作站名称'] = df['工作站名称'].ffill().astype(str).str.strip()
    df['类型'] = df['类型'].ffill()
    df['序号'] = df['序号'].ffill()
    
    print(f"✓ 共 {len(df)} 条原始记录")
    
    # 2. 拆分楼宇（处理换行符分隔的楼宇名称）
    print("\n[2/4] 拆分楼宇数据...")
    expanded_buildings = []
    building_id = 1
    
    for _, row in df.iterrows():
        if pd.isna(row['覆盖楼宇名称']) or row['覆盖楼宇名称'] == '':
            continue
        
        # 按换行符拆分
        building_names = str(row['覆盖楼宇名称']).split('\n')
        building_addrs = str(row['覆盖楼宇地址']).split('\n') if pd.notna(row['覆盖楼宇地址']) else []
        
        # 地址数量不够时补齐
        if len(building_addrs) < len(building_names):
            last_addr = building_addrs[-1] if building_addrs else ""
            building_addrs.extend([last_addr] * (len(building_names) - len(building_addrs)))
        
        # 为每个楼宇创建记录
        for i, building_name in enumerate(building_names):
            building_name = building_name.strip()
            if building_name:
                building_addr = building_addrs[i].strip() if i < len(building_addrs) else ""
                
                # 补全地址
                if building_addr and not building_addr.startswith('北京') and not building_addr.startswith('石景山'):
                    full_addr = f"北京市石景山区{building_addr}"
                else:
                    full_addr = building_addr
                
                expanded_buildings.append({
                    "楼宇ID": building_id,
                    "楼宇名称": building_name,
                    "楼宇地址": building_addr,
                    "完整地址": full_addr,
                    "所属街道": row['填报单位'],
                    "所属工作站": row['工作站名称'],
                    "工作站类型": row['类型'],
                    "是否合署办公": row['是否合署办公'] if pd.notna(row['是否合署办公']) else "",
                    "备注": row['备注'] if pd.notna(row['备注']) else "",
                    "经度": "",
                    "纬度": "",
                    "重点企业类型": ""  # 待补充：小巨人企业/亿元楼宇/处级领导包企业
                })
                building_id += 1
    
    print(f"✓ 拆分后 {len(expanded_buildings)} 个独立楼宇")
    
    # 3. 按工作站组织
    print("\n[3/4] 按工作站组织数据...")
    districts = sorted(df['填报单位'].unique())
    stations_summary = {}
    
    for district in districts:
        district_df = df[df['填报单位'] == district]
        stations = district_df['工作站名称'].unique()
        
        for station in stations:
            if not station or station == 'nan':
                continue
            
            station_df = district_df[district_df['工作站名称'] == station]
            station_buildings = [b for b in expanded_buildings if b['所属工作站'] == station]
            station_addr = station_buildings[0]['完整地址'] if station_buildings else ""
            
            station_key = f"{district}_{station}"
            stations_summary[station_key] = {
                "工作站名称": station,
                "工作站类型": station_df['类型'].iloc[0],
                "所属街道": district,
                "工作站地址": station_addr,
                "覆盖楼宇数量": len(station_buildings),
                "覆盖楼宇列表": station_buildings,
                "经度": "",
                "纬度": "",
                "备注": station_df['备注'].iloc[0] if pd.notna(station_df['备注'].iloc[0]) else ""
            }
    
    print(f"✓ 共 {len(stations_summary)} 个工作站")
    
    # 4. 生成11张地图
    print("\n[4/4] 生成11张地图数据...")
    map_data_collection = {}
    
    # 区级地图
    district_summary = []
    for district in districts:
        district_stations = [v for k, v in stations_summary.items() if v['所属街道'] == district]
        district_buildings = [b for b in expanded_buildings if b['所属街道'] == district]
        
        district_summary.append({
            "街道名称": district,
            "工作站数量": len(district_stations),
            "中心站数量": len([s for s in district_stations if s['工作站类型'] == '中心站']),
            "普通站数量": len([s for s in district_stations if s['工作站类型'] == '普通站']),
            "楼宇数量": len(district_buildings)
        })
    
    map_data_collection['01_石景山区定点定位图'] = {
        "地图名称": "石景山区定点定位图",
        "地图类型": "区级",
        "数据": {
            "街道列表": district_summary,
            "工作站点位": list(stations_summary.values())
        },
        "统计": {
            "街道/园区数": len(districts),
            "工作站总数": len(stations_summary),
            "楼宇总数": len(expanded_buildings)
        }
    }
    
    # 街道/园区级地图
    map_index = 2
    for district in districts:
        district_stations = [v for k, v in stations_summary.items() if v['所属街道'] == district]
        district_buildings = [b for b in expanded_buildings if b['所属街道'] == district]
        
        map_key = f"{map_index:02d}_{district}定点定位图"
        map_data_collection[map_key] = {
            "地图名称": f"{district}定点定位图",
            "地图类型": "街道/园区级",
            "所属街道": district,
            "数据": {
                "工作站列表": district_stations,
                "楼宇列表": district_buildings
            },
            "统计": {
                "工作站数量": len(district_stations),
                "楼宇数量": len(district_buildings)
            }
        }
        map_index += 1
    
    print(f"✓ 生成 {len(map_data_collection)} 张地图")
    
    # 5. 保存文件
    print("\n保存文件...")
    
    # 主数据文件
    with open('map_data.json', 'w', encoding='utf-8') as f:
        json.dump({
            "项目名称": "石景山区定点定位地图系统",
            "生成时间": "2025-11-03",
            "地图数量": len(map_data_collection),
            "地图数据": map_data_collection
        }, f, ensure_ascii=False, indent=2)
    print("  ✓ map_data.json - 11张地图的完整数据")
    
    # 地理编码文件
    buildings_df = pd.DataFrame(expanded_buildings)
    buildings_df.to_csv('addresses_geocoding.csv', index=False, encoding='utf-8-sig')
    print("  ✓ addresses_geocoding.csv - 待地理编码的楼宇地址")
    
    # 统计报告
    print("\n" + "="*80)
    print("处理完成！")
    print("="*80)
    print(f"\n✓ 工作站: {len(stations_summary)} 个")
    print(f"✓ 楼宇: {len(expanded_buildings)} 个")
    print(f"✓ 街道/园区: {len(districts)} 个")
    print(f"✓ 地图: {len(map_data_collection)} 张")
    
    print("\n下一步:")
    print("  1. 打开 addresses_geocoding.csv 对楼宇地址进行地理编码")
    print("  2. 在备注中标注重点企业类型")
    print("  3. 使用 map_data.json 在地图上显示")

if __name__ == '__main__':
    main()

